﻿//author: Sérgio Severino, nº 9733

#light
namespace FSharp.WinForms1

open System
open System.Collections.Generic
open System.ComponentModel
open System.Data
open System.Drawing
open System.Linq
open System.Text
open System.Windows.Forms
open System.IO

open Devart.Data; 
//Devart.Data.dll
//from: http://www.devart.com/dotconnect/sqlite/

open Devart.Data.SQLite; 
//Devart.Data.SQLite.dll
//from: http://www.devart.com/dotconnect/sqlite/

open LumenWorks.Framework.IO.Csv; 
//LumenWorks.Framework.IO.dll
//from: http://www.codeproject.com/KB/database/CsvReader.aspx


type Database = 
    val mutable differentYears : List<string>
    val mutable differentEstablishmentName : List<string> 
    val mutable differentDegree : List<string> 
    val mutable database : IEnumerable<List<string>>
    
    (*
    * A static method that converts a set of csv files
    * inside a folder to a sqlite database
    * receives a folder name
    * receives a sqlite filename
    *)
    static member createDatabase(folder : string, databaseName : string) =
        let appPath = Application.StartupPath + "/" + folder
        let fileExtension = "*.csv"
            

        // create a new database connection
        let sqlite_conn = new SQLiteConnection()
        sqlite_conn.ConnectionString <- "Data Source=" + databaseName + ";FailIfMissing=false;"
            
        sqlite_conn.Open()
            
        // BEGIN DEFERRED for faster insert
        let mutable sqlite_cmd = new SQLiteCommand("BEGIN", sqlite_conn)
        sqlite_cmd.ExecuteNonQuery() |> ignore
            
        sqlite_cmd <- new SQLiteCommand("DROP TABLE IF EXISTS tabela", sqlite_conn)
        sqlite_cmd.ExecuteNonQuery() |> ignore

        // Let the SQLiteCommand object know our SQL-Query:
        sqlite_cmd.CommandText <- "CREATE TABLE tabela (" +
                                            "CodigodoDocente varchar, " +
                                            "Nome varchar, " +
                                            "UltimoGrau varchar, " +
                                            "Curso varchar, " +
                                            "Categoria varchar, " +
                                            "RegimeDeTempo varchar, " +
                                            "AnoLectivo varchar, " +
                                            "TipoDeEstabelecimento varchar, " +
                                            "NomeDoEstabelecimento varchar" +
                                            ");"

        // Now lets execute the SQL ;D
        sqlite_cmd.ExecuteNonQuery() |> ignore

        let dir = new DirectoryInfo(appPath)
        try
        
            let files = dir.GetFiles(fileExtension)
            for file in files do
            
                let csv = new CsvReader(new StreamReader(file.FullName), true (*headers*))
                for line in csv do
                
                    sqlite_cmd.CommandText <- "INSERT INTO tabela (CodigodoDocente, " +
                                                    "Nome, " +
                                                    "UltimoGrau, " +
                                                    "Curso, " +
                                                    "Categoria, " +
                                                    "RegimeDeTempo, " +
                                                    "AnoLectivo, " +
                                                    "TipoDeEstabelecimento, " +
                                                    "NomeDoEstabelecimento) " +
                                                    "VALUES (" +
                                                    "'" + line.[0].Replace('\'', '☼') + "'," +
                                                    "'" + line.[1].Replace('\'', '☼') + "'," +
                                                    "'" + line.[2].Replace('\'', '☼') + "'," +
                                                    "'" + line.[3].Replace('\'', '☼') + "'," +
                                                    "'" + line.[4].Replace('\'', '☼') + "'," +
                                                    "'" + line.[5].Replace('\'', '☼') + "'," +
                                                    "'" + line.[6].Replace('\'', '☼') + "'," +
                                                    "'" + line.[7].Replace('\'', '☼') + "'," +
                                                    "'" + line.[8].Replace('\'', '☼') + "'" +
                                                    ");"

                    sqlite_cmd.ExecuteNonQuery() |> ignore

        finally
        
            sqlite_conn.Commit()
            sqlite_conn.Close()
        
    

    // constructor
    new () = 
        { differentYears = null;
          differentEstablishmentName = null;
          differentDegree = null;
          database = null; }

    
    (*
     * This is the method that
     * opens the sqlite database and copies it to a list
     *)
    member this.openDatabase(databaseName : string) = 
    
        this.differentYears <- new List<string>()
        this.differentEstablishmentName <- new List<string>()
        this.differentDegree <- new List<string>()
        
        let mutable dbConnStr = ""
        dbConnStr <- "Data Source=" + databaseName + ";FailIfMissing=false;"
        
        // create a new database connection
        let sqlite_conn = new SQLiteConnection()
        sqlite_conn.ConnectionString <- dbConnStr
        sqlite_conn.Open()

        // BEGIN DEFERRED
        let sqlite_cmd = new SQLiteCommand("BEGIN", sqlite_conn)
        sqlite_cmd.ExecuteNonQuery() |> ignore

        sqlite_cmd.CommandText <- "SELECT * FROM tabela"

        // DataReader
        let sqlite_datareader = sqlite_cmd.ExecuteReader()

        let tempList = new List<List<string>>()

        while sqlite_datareader.Read() do // Read() returns true if there is still a result line to read
        
            let tempShortList = new List<string>()

            // Print out the content of the text field:
            for f in 0 .. sqlite_datareader.FieldCount - 1 do
                tempShortList.Add(((string)sqlite_datareader.[f]).Replace('☼', '\''));
            tempList.Add(tempShortList)
        

        this.database <- seq {
            for tempShortList in tempList do
                yield tempShortList }

        this.populateDiffYears(sqlite_conn)
        this.populateDiffEstablishmentName(sqlite_conn)
        this.populateDiffDegree(sqlite_conn)

        sqlite_cmd.CommandText <- "END"
        sqlite_cmd.ExecuteNonQuery() |> ignore

        sqlite_conn.Close()
    

    

    (*
     * This method populates a list with all 
     * the different years in the database
     *)
    member this.populateDiffYears(sqlite_conn : SQLiteConnection) = 

        let sqlite_cmd = new SQLiteCommand("SELECT DISTINCT AnoLectivo FROM tabela", sqlite_conn)

        // DataReader
        let sqlite_datareader = sqlite_cmd.ExecuteReader()

        while (sqlite_datareader.Read()) do // Read() returns true if there is still a result line to read
        
            let tempShortList = new List<string>()

            
            for f in 0 .. sqlite_datareader.FieldCount - 1 do
                this.differentYears.Add(((string)sqlite_datareader.[f]).Replace('☼', '\''))

        this.differentYears.Sort()
    
    (*
     * This method populates a list with all 
     * the different establishment names in the database
     *)
    member this.populateDiffEstablishmentName(sqlite_conn : SQLiteConnection) =
    
        let sqlite_cmd = 
            new SQLiteCommand("SELECT DISTINCT NomeDoEstabelecimento FROM tabela", sqlite_conn)

        // DataReader
        let sqlite_datareader = sqlite_cmd.ExecuteReader()

        while sqlite_datareader.Read() do // Read() returns true if there is still a result line to read
        
            let tempShortList = new List<string>()

            
            for f in 0 .. sqlite_datareader.FieldCount - 1 do
                this.differentEstablishmentName.Add(((string)sqlite_datareader.[f]).Replace('☼', '\''))
        
        this.differentEstablishmentName.Sort()
    

    (*
     * This method populates a list with all 
     * the different degrees in the database
     *)
    member this.populateDiffDegree(sqlite_conn : SQLiteConnection) =
    
        let sqlite_cmd = 
            new SQLiteCommand("SELECT DISTINCT UltimoGrau FROM tabela", sqlite_conn)

        // DataReader
        let sqlite_datareader = sqlite_cmd.ExecuteReader()

        while sqlite_datareader.Read() do // Read() returns true if there is still a result line to read
        
            let tempShortList = new List<string>()

            
            for f in 0 .. sqlite_datareader.FieldCount - 1 do
                this.differentDegree.Add(((string)sqlite_datareader.[f]).Replace('☼', '\''))
        
        this.differentDegree.Sort()
    

    (*
     * Return method for the list differentYears
     *)
    member this.getDifYears() : List<string> =
        this.differentYears
    

    (*
     * Return method for the list differentEstablishmentName
     *)
    member this.getDifEstablishmentName() : List<string> =
        this.differentEstablishmentName;


    (*
     * Return method for the list differentDegree
     *)
    member this.getDifDegree() : List<string> =
        this.differentDegree;
    

    (*
     * This method produces a list with all teachers in
     * the given argument
     *)
    member this.createListCsvTpDpY(selectedEstablishment : int, selectedDegree : int) = 
    
        let establishment = this.getDifEstablishmentName().[selectedEstablishment]
        let degree = this.getDifDegree().[selectedDegree]

        let list = seq { 
                    for tmpList in this.database do
                        if String.Equals( tmpList.[8], establishment) && String.Equals(tmpList.[2], degree) then
                            yield [| tmpList.[2]; tmpList.[8]; tmpList.[6]; tmpList.[1] |] }


        let filename = "list of teachers, " + establishment + " per " + degree + ".csv"
        let header = "Último Grau," +
                        "Nome Do Estabelecimento," +
                        "Ano Lectivo," +
                        "Nome do Professor"

        File.Delete(filename);
        let sw = new StreamWriter(filename, true)
        
        sw.WriteLine header

        for tempList in list do
            
            let str = new StringBuilder()
            for temp in tempList do
                str.Append(temp.ToString() + ",") |> ignore
            str.Remove(str.ToString().Length - 1, 1)  |> ignore
            sw.WriteLine(str.ToString())
        sw.Close()
    

    (* 
     * This method produces the statistics about
     * the given argument
     *)
    member this.createCsvTpDpY(selectedEstablishment : int, selectedDegree : int) =
    
        let establishment = this.getDifEstablishmentName().[selectedEstablishment]
        let degree = this.getDifDegree().[selectedDegree]

        let counter = new List<List<string>>()
        for year in this.getDifYears() do
            let quantity = 
                let list = seq {
                    for tmpList in this.database do
                        if String.Equals( tmpList.[8], establishment) 
                           && String.Equals( tmpList.[6], year)
                           && String.Equals( tmpList.[2], degree) then
                            yield tmpList }
                list.Count()

            let tmp = new List<string>()
            tmp.Add(year)
            tmp.Add(quantity.ToString())

            counter.Add(tmp)
        

        let list = seq {
            for quantity in counter do
                yield [| degree; establishment; quantity.[0]; quantity.[1] |]};


        let filename = establishment + " per " + degree + ".csv"
        let header = "Último Grau," +
                        "Nome Do Estabelecimento," +
                        "Ano Lectivo," +
                        "Quantidade de Professores"

        File.Delete(filename);
        let sw = new StreamWriter(filename, true)
        
        sw.WriteLine header

        for tempList in list do
            
            let str = new StringBuilder()
            for temp in tempList do
                str.Append(temp.ToString() + ",") |> ignore
            str.Remove(str.ToString().Length - 1, 1)  |> ignore
            sw.WriteLine(str.ToString())
        sw.Close()
    

    (*
     * This method produces a list with all teachers in
     * the given argument
     *)
    member this.createListCsvTpY(selectedEstablishment : int) = 
    
        let establishment = this.getDifEstablishmentName().[selectedEstablishment]

        let list = seq {
            for tmpList in this.database do
                if String.Equals(tmpList.[8], establishment) then
                    yield [| tmpList.[8]; tmpList.[6]; tmpList.[1] |] };

        let filename = "list of teachers, " + establishment + " per Year.csv"
        let header = "Nome Do Estabelecimento," +
                        "Ano Lectivo," +
                        "Nome do Professor"

        File.Delete(filename);
        let sw = new StreamWriter(filename, true)
        
        sw.WriteLine header

        for tempList in list do
            
            let str = new StringBuilder()
            for temp in tempList do
                str.Append(temp.ToString() + ",") |> ignore
            str.Remove(str.ToString().Length - 1, 1)  |> ignore
            sw.WriteLine(str.ToString())
        sw.Close()
    

   (*
     * This method produces the statistics about
     * the given argument
     *)
    member this.createCsvTpY(selectedEstablishment : int) =
    
        let establishment = this.getDifEstablishmentName().[selectedEstablishment]

        let counter = new List<List<string>>()
        for year in this.getDifYears() do
        
            let quantity = 
                let list = seq {
                    for tmpList in this.database do
                        if String.Equals( tmpList.[8], establishment) 
                           && String.Equals( tmpList.[6], year ) then
                            yield tmpList }
                list.Count()

            let tmp = new List<string>()
            tmp.Add(year)
            tmp.Add(quantity.ToString())

            counter.Add(tmp)
        

        let list = seq {
            for quantity in counter do
                yield [| establishment; quantity.[0]; quantity.[1] |] };


        let filename = establishment + " per Year.csv"
        let header = "Nome Do Estabelecimento," +
                        "Ano Lectivo," +
                        "Quantidade de Professores"

        File.Delete(filename)
        let sw = new StreamWriter(filename, true)
        
        sw.WriteLine header;

        for tempList in list do
            
            let str = new StringBuilder()
            for temp in tempList do
                str.Append(temp.ToString() + ",") |> ignore
            str.Remove(str.ToString().Length - 1, 1)  |> ignore
            sw.WriteLine(str.ToString())
        sw.Close()


(*
* This is the class that generates
* all the interface related
*)
type public MainForm =
    inherit Form

    val mutable data : Database
    val mutable databaseName : string
    val mutable hasStatsMenu : bool

    val mutable menuStrip1 : System.Windows.Forms.MenuStrip
    val mutable fileToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable exitToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable createDatabaseToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable openDatabaseToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable statusStrip1 : System.Windows.Forms.StatusStrip 
    val mutable toolStripStatusLabel1 : System.Windows.Forms.ToolStripStatusLabel 
    val mutable toolStripStatusLabel2 : System.Windows.Forms.ToolStripStatusLabel 
    val mutable toolStripStatusLabel3 : System.Windows.Forms.ToolStripStatusLabel 
    val mutable databaseToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable createStatisticsOfToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 
    val mutable teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem : System.Windows.Forms.ToolStripMenuItem 

    val mutable basePanel : System.Windows.Forms.Panel 
    val mutable teacherpEpYpanel : System.Windows.Forms.Panel 
    val mutable teacherpDpEpYpanel : System.Windows.Forms.Panel 
    val mutable label1 : System.Windows.Forms.Label 
    val mutable label4 : System.Windows.Forms.Label 
    val mutable label5 : System.Windows.Forms.Label
    val mutable label6 : System.Windows.Forms.Label 
    val mutable label3 : System.Windows.Forms.Label 
    val mutable label2 : System.Windows.Forms.Label 
    val mutable showPanel : System.Windows.Forms.Panel 
    val mutable label7 : System.Windows.Forms.Label 
    val mutable es1ComboBox : System.Windows.Forms.ComboBox 
    val mutable label9 : System.Windows.Forms.Label 
    val mutable de2ComboBox : System.Windows.Forms.ComboBox 
    val mutable label8 : System.Windows.Forms.Label 
    val mutable es2ComboBox : System.Windows.Forms.ComboBox 
    val mutable button2 : System.Windows.Forms.Button 
    val mutable button1 : System.Windows.Forms.Button 
    val mutable button3 : System.Windows.Forms.Button 
    val mutable button4 : System.Windows.Forms.Button 

    val mutable components : System.ComponentModel.IContainer

    override this.Dispose (disposing) =
        if disposing && (this.components <> null) then
            this.components.Dispose ()
        base.Dispose (disposing)

    // TODO define your controls
    val mutable formLabel : Label 
    
    // TODO initialize your controls
    member this.initControls() = 
        this.formLabel.Text <- "Main form data"
        this.menuStrip1 <- new System.Windows.Forms.MenuStrip()
        this.fileToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.createDatabaseToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.openDatabaseToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.exitToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.statusStrip1 <- new System.Windows.Forms.StatusStrip()
        this.toolStripStatusLabel1 <- new System.Windows.Forms.ToolStripStatusLabel()
        this.toolStripStatusLabel2 <- new System.Windows.Forms.ToolStripStatusLabel()
        this.toolStripStatusLabel3 <- new System.Windows.Forms.ToolStripStatusLabel()
        this.createStatisticsOfToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()
        this.databaseToolStripMenuItem <- new System.Windows.Forms.ToolStripMenuItem()

        this.basePanel <- new System.Windows.Forms.Panel()
        this.teacherpDpEpYpanel <- new System.Windows.Forms.Panel()
        this.button2 <- new System.Windows.Forms.Button()
        this.button1 <- new System.Windows.Forms.Button()
        this.label9 <- new System.Windows.Forms.Label()
        this.de2ComboBox <- new System.Windows.Forms.ComboBox()
        this.label8 <- new System.Windows.Forms.Label()
        this.es2ComboBox <- new System.Windows.Forms.ComboBox()
        this.teacherpEpYpanel <- new System.Windows.Forms.Panel()
        this.button3 <- new System.Windows.Forms.Button()
        this.button4 <- new System.Windows.Forms.Button()
        this.label7 <- new System.Windows.Forms.Label()
        this.es1ComboBox <- new System.Windows.Forms.ComboBox()
        this.showPanel <- new System.Windows.Forms.Panel()
        this.label4 <- new System.Windows.Forms.Label()
        this.label5 <- new System.Windows.Forms.Label()
        this.label6 <- new System.Windows.Forms.Label()
        this.label3 <- new System.Windows.Forms.Label()
        this.label2 <- new System.Windows.Forms.Label()
        this.label1 <- new System.Windows.Forms.Label()

        this.menuStrip1.SuspendLayout()
        this.statusStrip1.SuspendLayout()

        this.basePanel.SuspendLayout()
        this.teacherpDpEpYpanel.SuspendLayout()
        this.teacherpEpYpanel.SuspendLayout()
        this.showPanel.SuspendLayout()

        this.SuspendLayout()
        // 
        // menuStrip1
        // 
        this.menuStrip1.Items.AddRange([| this.fileToolStripMenuItem :> System.Windows.Forms.ToolStripItem |])
        this.menuStrip1.Location <- System.Drawing.Point(0, 0)
        this.menuStrip1.Name <- "menuStrip1"
        this.menuStrip1.Size <- System.Drawing.Size(461, 24)
        this.menuStrip1.TabIndex <- 0
        this.menuStrip1.Text <- "menuStrip1"
        // 
        // fileToolStripMenuItem
        // 
        this.fileToolStripMenuItem.DropDownItems.AddRange([| (this.createDatabaseToolStripMenuItem :> System.Windows.Forms.ToolStripItem);
                                                             (this.openDatabaseToolStripMenuItem :> System.Windows.Forms.ToolStripItem);
                                                             (this.exitToolStripMenuItem :> System.Windows.Forms.ToolStripItem) |])
        this.fileToolStripMenuItem.Name <- "fileToolStripMenuItem"
        this.fileToolStripMenuItem.Size <- System.Drawing.Size(37, 20)
        this.fileToolStripMenuItem.Text <- "File"
        // 
        // createDatabaseToolStripMenuItem
        // 
        this.createDatabaseToolStripMenuItem.Name <- "createDatabaseToolStripMenuItem"
        this.createDatabaseToolStripMenuItem.Size <- System.Drawing.Size(158, 22)
        this.createDatabaseToolStripMenuItem.Text <- "Create database"
        this.createDatabaseToolStripMenuItem.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.createDatabaseMenuItem_Click(sender, e)))
        // 
        // openDatabaseToolStripMenuItem
        // 
        this.openDatabaseToolStripMenuItem.Name <- "openDatabaseToolStripMenuItem"
        this.openDatabaseToolStripMenuItem.Size <- System.Drawing.Size(158, 22)
        this.openDatabaseToolStripMenuItem.Text <- "Open database"
        this.openDatabaseToolStripMenuItem.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.openDatabaseMenuItem_Click(sender, e)))
        // 
        // exitToolStripMenuItem
        // 
        this.exitToolStripMenuItem.Name <- "exitToolStripMenuItem"
        this.exitToolStripMenuItem.Size <- System.Drawing.Size(158, 22)
        this.exitToolStripMenuItem.Text <- "Exit"
        this.exitToolStripMenuItem.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.exitMenuItem_Click(sender, e)))
        // 
        // statusStrip1
        // 
        this.statusStrip1.Items.AddRange([| (this.toolStripStatusLabel1 :> System.Windows.Forms.ToolStripItem);
                                            (this.toolStripStatusLabel2 :> System.Windows.Forms.ToolStripItem);
                                            (this.toolStripStatusLabel3 :> System.Windows.Forms.ToolStripItem); |]);
        this.statusStrip1.Location <- System.Drawing.Point(0, 227)
        this.statusStrip1.Name <- "statusStrip1"
        this.statusStrip1.Size <-System.Drawing.Size(461, 22)
        this.statusStrip1.TabIndex <- 1
        this.statusStrip1.Text <- "statusStrip1"
        // 
        // toolStripStatusLabel1
        // 
        this.toolStripStatusLabel1.Name <- "toolStripStatusLabel1"
        this.toolStripStatusLabel1.Size <- System.Drawing.Size(39, 17)
        this.toolStripStatusLabel1.Text <- "Status"
        // 
        // toolStripStatusLabel2
        // 
        this.toolStripStatusLabel2.Name <- "toolStripStatusLabel2"
        this.toolStripStatusLabel2.Size <- System.Drawing.Size(16, 17)
        this.toolStripStatusLabel2.Text <- ":  "
        // 
        // toolStripStatusLabel3
        // 
        this.toolStripStatusLabel3.Name <- "toolStripStatusLabel3"
        this.toolStripStatusLabel3.Size <- new System.Drawing.Size(32, 17)
        this.toolStripStatusLabel3.Text <- "_____"
        // 
        // createStatisticsOfToolStripMenuItem
        // 
        this.createStatisticsOfToolStripMenuItem.Name <- "createStatisticsOfToolStripMenuItem"
        this.createStatisticsOfToolStripMenuItem.Size <- System.Drawing.Size(152, 22)
        this.createStatisticsOfToolStripMenuItem.Text <- "Create statistics of"
        // 
        // teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem
        // 
        this.teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem.Name <- "teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem"
        this.teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem.Size <- System.Drawing.Size(152, 22)
        this.teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem.Text <- " teachers in the higher education system per establishment per year"
        this.teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.showTeacherpEpYpanel_evt( sender, e )))
        // 
        // teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem
        // 
        this.teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem.Name <- "teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem"
        this.teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem.Size <- System.Drawing.Size(152, 22)
        this.teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem.Text <- " teachers divided per type of degree per establishment and per year"
        this.teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.showTeacherpDpEpYpanel_evt(sender, e)))
        // 
        // databaseToolStripMenuItem
        // 
        this.databaseToolStripMenuItem.Name <- "databaseToolStripMenuItem"
        this.databaseToolStripMenuItem.Size <- System.Drawing.Size(12, 20)
        this.databaseToolStripMenuItem.Text <- "Statistics"
        // 
        // basePanel
        // 
        this.basePanel.Controls.Add(this.teacherpDpEpYpanel)
        this.basePanel.Controls.Add(this.teacherpEpYpanel)
        this.basePanel.Controls.Add(this.showPanel)
        this.basePanel.Dock <- System.Windows.Forms.DockStyle.Fill
        this.basePanel.Location <- System.Drawing.Point(0, 24)
        this.basePanel.Name <- "basePanel"
        this.basePanel.Size <- System.Drawing.Size(461, 203)
        this.basePanel.TabIndex <- 2
        // 
        // teacherpDpEpYpanel
        // 
        this.teacherpDpEpYpanel.Controls.Add(this.button2)
        this.teacherpDpEpYpanel.Controls.Add(this.button1)
        this.teacherpDpEpYpanel.Controls.Add(this.label9)
        this.teacherpDpEpYpanel.Controls.Add(this.de2ComboBox)
        this.teacherpDpEpYpanel.Controls.Add(this.label8)
        this.teacherpDpEpYpanel.Controls.Add(this.es2ComboBox)
        this.teacherpDpEpYpanel.Dock <- System.Windows.Forms.DockStyle.Fill
        this.teacherpDpEpYpanel.Location <- System.Drawing.Point(0, 0)
        this.teacherpDpEpYpanel.Name <- "teacherpDpEpYpanel"
        this.teacherpDpEpYpanel.Size <- System.Drawing.Size(461, 203)
        this.teacherpDpEpYpanel.TabIndex <- 1
        this.teacherpDpEpYpanel.Visible <- false
        // 
        // button2
        // 
        this.button2.Location <- System.Drawing.Point(269, 155)
        this.button2.Name <- "button2"
        this.button2.Size <- System.Drawing.Size(104, 23)
        this.button2.TabIndex <- 7
        this.button2.Text <- "Teacher list"
        this.button2.UseVisualStyleBackColor <- true
        this.button2.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.listTpEpDpYButton2_Click(sender, e)))
        // 
        // button1
        // 
        this.button1.Location <- System.Drawing.Point(100, 155)
        this.button1.Name <- "button1"
        this.button1.Size <- System.Drawing.Size(99, 23)
        this.button1.TabIndex <- 6
        this.button1.Text <- "Create csv"
        this.button1.UseVisualStyleBackColor <- true
        this.button1.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.csvTpEpDpYButton1_Click(sender, e)))
        // 
        // label9
        // 
        this.label9.AutoSize <- true
        this.label9.Location <- System.Drawing.Point(186, 81)
        this.label9.Name <- "label9"
        this.label9.Size <- System.Drawing.Size(42, 13)
        this.label9.TabIndex <- 5
        this.label9.Text <- "Degree"
        // 
        // de2ComboBox
        // 
        this.de2ComboBox.FormattingEnabled <- true
        this.de2ComboBox.Location <- System.Drawing.Point(27, 106)
        this.de2ComboBox.Name <- "de2ComboBox"
        this.de2ComboBox.Size <- System.Drawing.Size(407, 21)
        this.de2ComboBox.TabIndex <- 4
        // 
        // label8
        // 
        this.label8.AutoSize <- true
        this.label8.Location <- System.Drawing.Point(171, 19)
        this.label8.Name <- "label8"
        this.label8.Size <- System.Drawing.Size(72, 13)
        this.label8.TabIndex <- 3
        this.label8.Text <- "Establishment"
        // 
        // es2ComboBox
        // 
        this.es2ComboBox.FormattingEnabled <- true
        this.es2ComboBox.Location <- System.Drawing.Point(27, 42)
        this.es2ComboBox.Name <- "es2ComboBox"
        this.es2ComboBox.Size <- System.Drawing.Size(407, 21)
        this.es2ComboBox.TabIndex <- 2
        // 
        // teacherpEpYpanel
        // 
        this.teacherpEpYpanel.Controls.Add(this.button3)
        this.teacherpEpYpanel.Controls.Add(this.button4)
        this.teacherpEpYpanel.Controls.Add(this.label7)
        this.teacherpEpYpanel.Controls.Add(this.es1ComboBox)
        this.teacherpEpYpanel.Dock <- System.Windows.Forms.DockStyle.Fill
        this.teacherpEpYpanel.Location <- new System.Drawing.Point(0, 0)
        this.teacherpEpYpanel.Name <- "teacherpEpYpanel"
        this.teacherpEpYpanel.Size <- System.Drawing.Size(461, 203)
        this.teacherpEpYpanel.TabIndex <- 0
        this.teacherpEpYpanel.Visible <- false
        // 
        // button3
        // 
        this.button3.Location <- System.Drawing.Point(269, 155)
        this.button3.Name <- "button3"
        this.button3.Size <- System.Drawing.Size(104, 23)
        this.button3.TabIndex <- 9
        this.button3.Text <- "Teacher list"
        this.button3.UseVisualStyleBackColor <- true
        this.button3.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.listTpEpYButton3_Click(sender, e)))
        // 
        // button4
        // 
        this.button4.Location <- System.Drawing.Point(100, 155)
        this.button4.Name <- "button4"
        this.button4.Size <- System.Drawing.Size(99, 23)
        this.button4.TabIndex <- 8
        this.button4.Text <- "Create csv"
        this.button4.UseVisualStyleBackColor <- true
        this.button4.Click.AddHandler(
                new System.EventHandler (fun sender e -> this.csvTpEpYBButton4_Click(sender, e)))
        // 
        // label7
        // 
        this.label7.AutoSize <- true
        this.label7.Location <- System.Drawing.Point(171, 19)
        this.label7.Name <- "label7"
        this.label7.Size <- System.Drawing.Size(72, 13)
        this.label7.TabIndex <- 1
        this.label7.Text <- "Establishment"
        // 
        // es1ComboBox
        // 
        this.es1ComboBox.FormattingEnabled <- true
        this.es1ComboBox.Location <- System.Drawing.Point(27, 42)
        this.es1ComboBox.Name <- "es1ComboBox"
        this.es1ComboBox.Size <- System.Drawing.Size(407, 21)
        this.es1ComboBox.TabIndex <- 0
        // 
        // showPanel
        // 
        this.showPanel.Controls.Add(this.label4)
        this.showPanel.Controls.Add(this.label5)
        this.showPanel.Controls.Add(this.label6)
        this.showPanel.Controls.Add(this.label3)
        this.showPanel.Controls.Add(this.label2)
        this.showPanel.Controls.Add(this.label1)
        this.showPanel.Dock <- System.Windows.Forms.DockStyle.Fill
        this.showPanel.Location <- System.Drawing.Point(0, 0)
        this.showPanel.Name <- "showPanel"
        this.showPanel.Size <- System.Drawing.Size(461, 203)
        this.showPanel.TabIndex <- 3
        // 
        // label4
        // 
        this.label4.AutoSize <- true
        this.label4.Location <- System.Drawing.Point(156, 34)
        this.label4.Name <- "label4"
        this.label4.Size <- System.Drawing.Size(103, 13)
        this.label4.TabIndex <- 5
        this.label4.Text <- "Create the database"
        // 
        // label5
        // 
        this.label5.AutoSize <- true
        this.label5.Location <- System.Drawing.Point(134, 34)
        this.label5.Name <- "label5"
        this.label5.Size <- System.Drawing.Size(16, 13)
        this.label5.TabIndex <- 4
        this.label5.Text <- "to"
        // 
        // label6
        // 
        this.label6.AutoSize <- true
        this.label6.Location <- System.Drawing.Point(12, 34)
        this.label6.Name <- "label6"
        this.label6.Size <- System.Drawing.Size(116, 13)
        this.label6.TabIndex <- 3
        this.label6.Text <- "File -> Create database"
        // 
        // label3
        // 
        this.label3.AutoSize <- true
        this.label3.Location <- System.Drawing.Point(151, 114)
        this.label3.Name <- "label3"
        this.label3.Size <- System.Drawing.Size(246, 13)
        this.label3.TabIndex <- 2
        this.label3.Text <- "Open the database and unlock the Statistics menu"
        // 
        // label2
        // 
        this.label2.AutoSize <- true
        this.label2.Location <- new System.Drawing.Point(129, 114)
        this.label2.Name <- "label2"
        this.label2.Size <- System.Drawing.Size(16, 13)
        this.label2.TabIndex <- 1
        this.label2.Text <- "to"
        // 
        // label1
        // 
        this.label1.AutoSize <- true
        this.label1.Location <- new System.Drawing.Point(12, 114)
        this.label1.Name <- "label1"
        this.label1.Size <- new System.Drawing.Size(111, 13)
        this.label1.TabIndex <- 0
        this.label1.Text <- "File -> Open database"
        // 
        // Form1
        // 
        this.AutoScaleDimensions <- System.Drawing.SizeF(6.0F, 13.0F)
        this.AutoScaleMode <- System.Windows.Forms.AutoScaleMode.Font
        this.AutoSizeMode <- System.Windows.Forms.AutoSizeMode.GrowAndShrink
        this.ClientSize <- System.Drawing.Size(461, 249)
        this.Controls.Add(this.basePanel)
        this.Controls.Add(this.statusStrip1)
        this.Controls.Add(this.menuStrip1)
        this.MainMenuStrip <- this.menuStrip1
        this.MaximizeBox <- false
        this.Name <- "Form1"
        this.StartPosition <- System.Windows.Forms.FormStartPosition.CenterScreen
        this.Text <- "LP TP2 F#"
        this.menuStrip1.ResumeLayout(false)
        this.menuStrip1.PerformLayout()
        this.statusStrip1.ResumeLayout(false)
        this.statusStrip1.PerformLayout()
        this.basePanel.ResumeLayout(false)
        this.teacherpDpEpYpanel.ResumeLayout(false)
        this.teacherpDpEpYpanel.PerformLayout()
        this.teacherpEpYpanel.ResumeLayout(false)
        this.teacherpEpYpanel.PerformLayout()
        this.showPanel.ResumeLayout(false)
        this.showPanel.PerformLayout()
        this.ResumeLayout(false)
        this.PerformLayout()

    // constructor
    new () as this  = 
        { data = new Database();
          databaseName = "database.db";
          hasStatsMenu = false;
          menuStrip1 = null;
          fileToolStripMenuItem = null;
          exitToolStripMenuItem = null;
          createDatabaseToolStripMenuItem = null;
          openDatabaseToolStripMenuItem = null;
          statusStrip1 = null;
          toolStripStatusLabel1 = null;
          toolStripStatusLabel2 = null;
          toolStripStatusLabel3 = null;
          databaseToolStripMenuItem = null;
          createStatisticsOfToolStripMenuItem = null;
          teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem = null;
          teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem = null;
          components = null;
          formLabel = new Label(); 
          basePanel = null;
          teacherpEpYpanel = null;
          teacherpDpEpYpanel = null; 
          label1 = null;
          label4 = null;
          label5 = null;
          label6 = null;
          label3 = null;
          label2 = null;
          showPanel = null;
          label7 = null;
          es1ComboBox = null;
          label9 = null;
          de2ComboBox = null;
          label8 = null;
          es2ComboBox = null;
          button2 = null;
          button1 = null;
          button3 = null;
          button4 = null; }
        then
          this.initControls()
          this.changeStatus "Waiting for action"

          
    // to add the database menu when needed
    member this.addDatabaseMenu() =
        // 
        // databaseToolStripMenuItem
        // 
        this.menuStrip1.Items.AddRange([| (this.databaseToolStripMenuItem :> System.Windows.Forms.ToolStripItem) |]);

        // 
        // createStatisticsOfToolStripMenuItem
        // 
        this.databaseToolStripMenuItem.DropDownItems.AddRange([| (this.createStatisticsOfToolStripMenuItem :> System.Windows.Forms.ToolStripItem) |]);

        // 
        // teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem
        // and
        // teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem
        // 
        this.createStatisticsOfToolStripMenuItem.DropDownItems.AddRange([| (this.teachersInTheHigherEducationSystemPerEstablishmentPerYearToolStripMenuItem :> System.Windows.Forms.ToolStripItem);
                                                                           (this.teachersDividedPerTypeOfDegreePerEstablishmentAndPerYearToolStripMenuItem :> System.Windows.Forms.ToolStripItem); |]);

        
    //change status description
    member this.changeStatus(str : string) = 
        this.toolStripStatusLabel3.Text <- str
        this.statusStrip1.Update()
        this.statusStrip1.Refresh()
        
        
    member this.hideBasePanel() =  this.showPanel.Hide()

    member this.showBasePanel() = this.showPanel.Show()

    member this.hideTeacherpEpYpanel() = this.teacherpEpYpanel.Hide()

    member this.showTeacherpEpYpanel() = this.teacherpEpYpanel.Show()

    member this.hideTeacherpDpEpYpanel() = this.teacherpDpEpYpanel.Hide()

    member this.showTeacherpDpEpYpanel() = this.teacherpDpEpYpanel.Show()

    // to add strings to the combo boxes when the database is opened
    member this.populateComboBoxes(database : Database) = 
        let mutable tmpList = 
            let list = new List<Object>()
            for name in database.getDifEstablishmentName() do
                list.Add(name) |> ignore
            list
        this.es1ComboBox.Items.AddRange(tmpList.ToArray())
        this.es2ComboBox.Items.AddRange(tmpList.ToArray())
        
        tmpList <- 
            let list = new List<Object>()
            for name in database.getDifDegree() do
                list.Add(name) |> ignore
            list
        this.de2ComboBox.Items.AddRange(tmpList.ToArray())
       

    ///////// EVENTS
    member this.createDatabaseMenuItem_Click(sender:obj, e:EventArgs) = 
        this.changeStatus("Creating database... Please be patient...")
        Database.createDatabase("rebcsv", "database.db")
        this.changeStatus("Waiting for action")

    member this.exitMenuItem_Click(sender:obj, e:EventArgs) =
        this.changeStatus("Shutting down");
        this.Dispose true

    member this.openDatabaseMenuItem_Click(sender:obj, e:EventArgs) =
     
        if (File.Exists(Application.StartupPath + "/" + this.databaseName)) then 
        
            this.changeStatus("Opening database... Please be patient...")
            this.data.openDatabase("database.db")

            if not this.hasStatsMenu then 
            
                this.addDatabaseMenu()
                this.hasStatsMenu <- true
            
            this.populateComboBoxes(this.data)
            this.changeStatus("Waiting for action")
        

    member this.showTeacherpDpEpYpanel_evt(sender:obj, e:EventArgs) =
        this.hideBasePanel()
        this.hideTeacherpEpYpanel()
        this.showTeacherpDpEpYpanel()

    member this.showTeacherpEpYpanel_evt(sender:obj, e:EventArgs) =
        this.hideBasePanel()
        this.hideTeacherpDpEpYpanel()
        this.showTeacherpEpYpanel()

    member this.csvTpEpDpYButton1_Click(sender:obj, e:EventArgs) =
        this.changeStatus("Generating file... Please be patient...")
        this.data.createCsvTpDpY(this.es2ComboBox.SelectedIndex, this.de2ComboBox.SelectedIndex)
        this.changeStatus("Waiting for action")

    member this.listTpEpDpYButton2_Click(sender:obj, e:EventArgs) =
        this.changeStatus("Generating file... Please be patient...")
        this.data.createListCsvTpDpY(this.es2ComboBox.SelectedIndex, this.de2ComboBox.SelectedIndex)
        this.changeStatus("Waiting for action")

    member this.listTpEpYButton3_Click(sender:obj, e:EventArgs) =
        this.changeStatus("Generating file... Please be patient...")
        this.data.createListCsvTpY(this.es1ComboBox.SelectedIndex)
        this.changeStatus("Waiting for action")

    member this.csvTpEpYBButton4_Click(sender:obj, e:EventArgs) =
        this.changeStatus("Generating file... Please be patient...")
        this.data.createCsvTpY(this.es1ComboBox.SelectedIndex)
        this.changeStatus("Waiting for action")


module Main =

    [<STAThread>]
    [<EntryPoint>]
    do
        Application.EnableVisualStyles()
        Application.SetCompatibleTextRenderingDefault(false)
        Application.Run(new MainForm() :> Form) 